using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class KhachThuePhongDAO
    {
        private KhachThuePhongDTO[] ConvertDTtoArr(DataTable dt)
        {
            int n = dt.Rows.Count;
            KhachThuePhongDTO[] KT = new KhachThuePhongDTO[n];
            for (int i = 0; i < n; i++)
            {
                KT[i] = new KhachThuePhongDTO();
                KT[i].MaKT = Convert.ToInt32(dt.Rows[i]["MaKT"].ToString());
                KT[i].HoTen = dt.Rows[i]["HoTen"].ToString();
                KT[i].CMND_Passport = dt.Rows[i]["CMND_PASSPORT"].ToString();
                KT[i].DienThoai = dt.Rows[i]["DienThoai"].ToString();
                KT[i].DiaChi = dt.Rows[i]["DiaChi"].ToString();
                KT[i].MaLK = Convert.ToInt32(dt.Rows[i]["MaLoaiKhach"].ToString());
            }
            return KT;
        }
        public KhachThuePhongDTO[] LayDanhSachKT()
        {
            KhachThuePhongDTO[] KT = new KhachThuePhongDTO[100];
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                DataTable dt = new DataTable();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_layDSKhachThue";
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                KT = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return KT;
        }
        public int ThemKhachThue_Output(int MaKT, string HoTen, string CMND, string DienThoai, string DiaChi, int LoaiKhach)
        {
            MaKT = 0;
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemKhachThuePhong_Output";

                cmd.Parameters.Add("@MaKT",SqlDbType.Int);
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                cmd.Parameters.Add("@CMND", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                cmd.Parameters.Add("@MaLK", SqlDbType.Int);

                cmd.Parameters["@MaKT"].Direction = ParameterDirection.Output;
                cmd.Parameters["@HoTen"].Value = HoTen;
                cmd.Parameters["@CMND"].Value = CMND;
                cmd.Parameters["@DienThoai"].Value = DienThoai;
                cmd.Parameters["@DiaChi"].Value = DiaChi;
                cmd.Parameters["@MaLK"].Value = LoaiKhach;
                cmd.ExecuteNonQuery();

                MaKT = Convert.ToInt32(cmd.Parameters["@MaKT"].Value);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return MaKT;
        }

        public void ThemKhachThue(string HoTen, string CMND, string DienThoai, string DiaChi, int LoaiKhach)
        {
            KhachThuePhongDTO ktpDTO = new KhachThuePhongDTO();
            ktpDTO.HoTen = HoTen;
            ktpDTO.CMND_Passport = CMND;
            ktpDTO.DienThoai = DienThoai;
            ktpDTO.DiaChi = DiaChi;
            ktpDTO.MaLK = LoaiKhach;

            ThemKhachThue(ktpDTO);
        }

        private void ThemKhachThue(KhachThuePhongDTO kt)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemKhachThuePhong";

                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                cmd.Parameters.Add("@CMND", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                cmd.Parameters.Add("@MaLK", SqlDbType.Int);

                cmd.Parameters["@HoTen"].Value = kt.HoTen;
                cmd.Parameters["@CMND"].Value = kt.CMND_Passport;
                cmd.Parameters["@DienThoai"].Value = kt.DienThoai;
                cmd.Parameters["@DiaChi"].Value = kt.DiaChi;
                cmd.Parameters["@MaLK"].Value = kt.MaLK;
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        public KhachThuePhongDTO[] TimKiemKhachThue(int MaKT)
        {
            KhachThuePhongDTO[] kt = new KhachThuePhongDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strMaKT = " 1 = 1 ";
                if (MaKT != 0)
                    strMaKT = " MaKT = " + MaKT;
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM KhachThuePhong WHERE ";
                strSQL += strMaKT;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                kt = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return kt;
        }
        public KhachThuePhongDTO[] TimKiemNangCao(KhachThuePhongDTO kt)
        {
            KhachThuePhongDTO[] arr = new KhachThuePhongDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strTenKT = " 1 = 1 ";
                string strCMND = " 1 = 1 ";
                string strDienThoai = " 1 = 1 ";
                string strDiaChi = " 1 = 1 ";
                string strMaLK = " 1 = 1 ";

                if (string.Compare(kt.HoTen, "") != 0)
                    strTenKT = " HoTen like N'%" + kt.HoTen + "%'";
                if (string.Compare(kt.CMND_Passport, "") != 0)
                    strCMND = " CMND_PASSPORT like N'%" + kt.CMND_Passport + "%'";
                if (string.Compare(kt.DienThoai, "") != 0)
                    strDienThoai = " DienThoai like N'%" + kt.DienThoai + "%'";
                if (string.Compare(kt.DiaChi, "") != 0)
                    strDiaChi = " DiaChi like N'%" + kt.DiaChi + "%'";
                if (kt.MaLK != 0)
                    strMaLK = " MaLoaiKhach = " + kt.MaLK;

                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM KhachThuePhong WHERE ";
                strSQL += strTenKT + " AND ";
                strSQL += strCMND + " AND ";
                strSQL += strDienThoai + " AND ";
                strSQL += strDiaChi + " AND ";
                strSQL += strMaLK;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                arr = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return arr;
        }
    }
}
